<?php

require("dbinfo.php");

function sql_connect()
{
  require("dbinfo.php");
	mysql_connect($db_host, $db_username, $db_password) or die( "Unable to connect");
	mysql_select_db($db_database) or die("Unable to select database");
}

function sql_login($username, $password)
{
    sql_connect();
	  $query = "
	  select		*	
	  from		Users 
	  where		Email=\"$username\" and Password=\"$password\"";

	  $result = mysql_query ($query)  or die(mysql_error());  

	  if(mysql_num_rows($result) == 1)
	  {           
        $row = mysql_fetch_assoc($result);
		    return $row["IsAdmin"];
	  }
	  else
	  {
		    return -1;
	  }  
}

function sql_getEmployees()
{
  sql_connect();
  $query = "select * from Employees
  order by LastName asc";
  
  $result = mysql_query ($query)  or die(mysql_error()); 
  return $result;
}

function sql_getEmployee($email)
{
  sql_connect();
  $query = "select * from Employees
  where Email=\"$email\" ";
  
  $result = mysql_query ($query)  or die(mysql_error());  
  
  $row = mysql_fetch_assoc($result);
 
  return $row;
}

function sql_getUserProjects($user)
{
  sql_connect();
  $query = "select * from Project, TeamMembers 
  where TeamMembers.EmployeeEmail=\"$user\" and Project.ID=TeamMembers.ProjectID ";
  
  $result = mysql_query ($query)  or die(mysql_error()); 
  return $result;
}


function sql_getEmployeeTasks($email)
{
  sql_connect();
  
  $query = "select * from Task
  where EmployeeEmail=\"$email\" ";
  
  $result = mysql_query ($query)  or die(mysql_error()); 
  return $result;

}

function sql_getEmployeeOpenTasks($email)
{
  sql_connect();
  
  $query = "select * from Task
  where EmployeeEmail=\"$email\" and IsClosed=\"0\" ";
  
  $result = mysql_query ($query)  or die(mysql_error()); 
  return $result;

}


function sql_getEmployeeClosedTasks($email)
{
  sql_connect();
  
  $query = "select * from Task
  where EmployeeEmail=\"$email\" and IsClosed=\"1\" ";
  
  $result = mysql_query ($query)  or die(mysql_error()); 
  return $result;

}


function sql_createProject($user, $title, $desc, $team)
{

  sql_connect();
  $query = " insert into	Project 
  values ('', '$title', '$desc', '$user') ";
   
  $result = mysql_query ($query)  or die(mysql_error()); 
  $pid = mysql_insert_id();
  
  foreach ($team as $email)
  {
    sql_addUserToProject($email, $pid);
  }
  
  sql_addUserToProject($user, $pid);
  
  return sql_getProject($pid);
}

function sql_addUserToProject($email, $pid)
{
  sql_connect();
  $query = "insert into TeamMembers
  values ('$pid', '$email') ";
  
  $result = mysql_query ($query)  or die(mysql_error()); 

  return $result;
}


function sql_getProject($pid)
{

  sql_connect();
  $query = " 	select		* 
	from		Project
	where		ID=\"$pid\" ";
   
  $result = mysql_query ($query)  or die(mysql_error()); 
  
  if(mysql_num_rows($result) == 1)
	{ 
      $row = mysql_fetch_assoc($result);
      return $row;
  }
  else
  {
      return -1;
  }
}

function sql_removeUsersFromProject($pid)
{
  sql_connect();
  $query = "delete from TeamMembers
  where ProjectID=\"$pid\" ";
  
  $result = mysql_query ($query)  or die(mysql_error()); 

  return $result;
}


function sql_editProject($pid, $user, $title, $desc, $team)
{
  sql_connect();
  $query = "update		Project as p
	set			p.Title=\"$title\", p.Description=\"$desc\"
	where		p.ID=\"$pid\" ";
   
  $result = mysql_query ($query)  or die(mysql_error());
  
  sql_removeUsersFromProject($pid);
  foreach ($team as $email)
  {
    sql_addUserToProject($email, $pid);
  }
  
  sql_addUserToProject($user,$pid);
  
  return sql_getProject($pid);
}


function sql_getPossibleTeamMembers($manager)
{

  sql_connect();
 
	$query = "select FirstName, LastName, Email
	from		Users
	where		not Email=\"$manager\" and IsAdmin=\"0\"
	order by LastName ";
  
  
  $result = mysql_query ($query)  or die(mysql_error()); 
  
  return $result;
  
}

function sql_isOnProject($email, $pid)
{
  sql_connect();
  
  $query = "select *
  from      TeamMembers
  where     ProjectID=\"$pid\" and EmployeeEmail=\"$email\" ";
  
  $result = mysql_query ($query)  or die(mysql_error()); 
    
  if(mysql_num_rows($result) == 1)
	{  
    return 1;
  }
  else
  {
    return 0;
  }

}


function sql_getProjectMilestones($pid)
{
  sql_connect();

	$query = "select		*
	from		Milestones 
	where		ProjectID=\"$pid\"
	order by	DueDate asc";
  
  $result = mysql_query ($query)  or die(mysql_error());
  
  return $result;
}

function sql_getMilestoneTasks($pid, $msName)
{
  sql_connect();

  $query = "	
  select		*
	from		Task
	where		ProjectID=\"$pid\" and MilestoneName=\"$msName\"
	order by	DueDate ASC ";

  $result = mysql_query ($query)  or die(mysql_error());
  
  return $result;
}


function sql_editMilestone($pid, $oldName, $newName, $mDesc, $mDueDate, $isComplete)
{
  sql_connect();
  
	$query = "update		Milestones
	set			Name=\"$newName\", Description=\"$mDesc\",
  DueDate=\"$mDueDate\", IsComplete=\"$isComplete\"
	where		ProjectID=\"$pid\" and Name=\"$oldName\" ";

  $result = mysql_query ($query)  or die(mysql_error());
  $ms = sql_getMilestone($pid, $newName); 
  return $ms;
  
}


function sql_getMilestone($pid, $mName)
{
  sql_connect();
  
	$query = "select		*
	from		Milestones
	where		Name=\"$mName\" and ProjectID=\"$pid\" ";

  $result = mysql_query ($query)  or die(mysql_error());
  $row = mysql_fetch_assoc($result);
  return $row;
}


function sql_createMilestone($pid, $mName, $mDesc, $mDueDAte, $isComplete)
{
  sql_connect();
  
  $query = "	insert into Milestones
	values		( \"$pid\", \"$mName\", \"$mDesc\", \"$mDueDate\", \"$isComplete\")";
  
  $result = mysql_query ($query)  or die(mysql_error());
  $ms = sql_getMilestone($pid, $mName);
  
  return $ms;
}

function sql_getTask($pid, $mName, $tName)
{
  sql_connect();

  $query = "	select		*
	from		Task
	where		ProjectID=\"$pid\" and MilestoneName=\"$mName\" and TaskName=\"$tName\" ";

  $result = mysql_query ($query)  or die(mysql_error());
  $row = mysql_fetch_assoc($result);
  return $row;
}

function sql_createTask($pid, $mName, $tName, $tDesc, $tDueDate, $isClosed, $email)
{
  sql_connect();
  
  //-- Task duedate must be before or on milestone duedate
  
  $query = "insert into Task
	values		(\"$pid\", \"$mName\", \"$tName\", \"$tDesc\",
				 \"$dueDate\", \"$isClosed\", \"$email\") ";
    
  $result = mysql_query ($query)  or die(mysql_error());
  return sql_getTask($pid, $mName, $tName);
  
}

function sql_editTask($pid, $mName, $oldName, $newName, $tDesc, $tDueDate, $isClosed, $email)
{
  sql_connect();
	$query = "update		Task
	set			TaskName=\"$newName\", Description=\"$tDesc\", DueDate=\"$tDueDate\",
				IsClosed=\"$isClosed\", EmployeeEmail=\"$email\"
	where		ProjectID=\"$pid\" and MilestoneName=\"$mName\" and TaskName=\"$oldName\" ";

echo "$query";
  $result = mysql_query ($query)  or die(mysql_error());
  return sql_getTask($pid, $mName, $newName);

}

function sql_getUserTasks($user)
{
  $query = "select * from Task where EmployeeEmail=\"$user\" ";
  $result = mysql_query ($query)  or die(mysql_error());
  return $result;
}

function sql_getEmployeeCertifications($email)
{
  sql_connect();
  $query = "select * from EmployeeCertification
  where EmployeeEmail=\"$email\" ";
  
  $result = mysql_query ($query)  or die(mysql_error());
  return $result;
}

function sql_addCertification($email, $cert)
{
  sql_connect();
  $query = "insert  into EmployeeCertification
  values (\"$email\", \"$cert\") ";
  
  $result = mysql_query ($query)  or die(mysql_error());
  return $result;

}


function sql_createEmployee($firstName, $lastName, $email, $pw)
{
  sql_connect();
  
  $query = "insert into Users
  values (\"$email\", \"$firstName\", \"$lastName\", \"$pw\", 0)";
  
  $result = mysql_query ($query)  or die(mysql_error());

  if($result)
  {
    return sql_getEmployee($email);
  }
  else
  {
    return -1;
  }

}


function sql_editEmployee($firstName, $lastName, $oldEmail, $newEmail, $pw)
{
  sql_connect();
  
  $query = "update  Users  
  set Email=\"$newEmail\", FirstName=\"$firstName\", LastName=\"$lastName\", Password=\"$pw\", IsAdmin=\"0\" 
  where Email=\"$oldEmail\" ";
  
  $result = mysql_query ($query)  or die(mysql_error());

  if($result)
  {
    return sql_getEmployee($newEmail);
  }
  else
  {
    return -1;
  }

}

function sql_getProjectTasks($pid)
{
  sql_connect();
  $query = "select * from Task
  where ProjectID=\"$pid\" ";
  
  $result = mysql_query ($query)  or die(mysql_error());
  return $result;

}


function sql_createTimeTrack($pid, $mName, $tName, $date, $hours)
{
  sql_connect();
  
  $currhours = sql_getTaskHoursByDate($pid, $mName, $tName, $date);
  
  if($currhours > 0)
  {
    $hours = $hours + $currHours;
    $query = "update TimeTracking
    where ProjectID=\"$pid\" and MilestoneName=\"$mName\" and TaskName=\"$tName\" and DateWorked=\"$date\"
    set Hours=\"$hours\" ";
  } 
  else
  {
    $query = "insert into TimeTracking
    values (\"$pid\", \"$mName\", \"$tName\", \"$date\", \"$hours\")";
  }
  
  $result = mysql_query ($query)  or die(mysql_error());
  return $result;

}


function makeSQLDate($phpDate)
{
  return date('Y-m-d', strtotime($phpDate));
}

function makePHPDate($sqlDate)
{
  return  date('m/d/Y', strtotime($sqlDate));
}

function sql_getMilestoneHours($pid, $msName)
{
  sql_connect();
  $query = "select * from TimeTracking
  where ProjectID=\"$pid\" and MilestoneName=\"$msName\" ";
  
  $result = mysql_query ($query)  or die(mysql_error());
  
  	if(mysql_num_rows($result)  > 0)
	  {  
      $count = 0;
      while($row = mysql_fetch_array($result))
			{
        $count = $count + $row['Hours'];
      }
      return $count;
    }
    else
    {
      return 0;
    }
}

function sql_getTaskHours($pid, $msName, $tName)
{
  sql_connect();
  $query = "select * from TimeTracking
  where ProjectID=\"$pid\" and MilestoneName=\"$msName\" and TaskName=\"$tName\" ";
  
  $result = mysql_query ($query)  or die(mysql_error());
  
    if(mysql_num_rows($result)  > 0)
	  {  
      $count = 0;
      while($row = mysql_fetch_array($result))
			{
        $count = $count + $row['Hours'];
      }
      return $count;
    }
    else
    {
      return 0;
    }
  
}

function sql_getTaskHoursByDate($pid, $mName, $tName, $date)
{
  sql_connect();
  $query = "select * from TimeTracking
  where ProjectID=\"$pid\" and MilestoneName=\"$mName\" and TaskName=\"$tName\" and DateWorked=\"$date\" ";
  
  $result = mysql_query ($query)  or die(mysql_error());
  
    if(mysql_num_rows($result)  > 0)
	  {  
      $count = 0;
      while($row = mysql_fetch_array($result))
			{
        $count = $count + $row['Hours'];
      }
      return $count;
    }
    else
    {
      return 0;
    } 
  
}

function sql_getEmployeeHours($email)
{
  sql_connect();
  
  $tasks = sql_getEmployeeTasks($email);

    if(mysql_num_rows($tasks)  > 0)
	  {  
      $count = 0;
      while($row = mysql_fetch_array($tasks))
			{
        $tName = $row['TaskName'];
        $pid = $row['ProjectID'];
        $mName = $row['MilestoneName'];
        $hours = sql_getTaskHours($pid, $mName, $tName);
      
        $count = $count + $hours;
      }
      return $count;
    }
    else
    {
      return 0;
    } 
 
}

?>